<?php
/**
 * 验证手续费
 * Created by PhpStorm.
 * Author: 张志祥
 * QQ: 786811688
 * Phone: 13306281319
 * Date: 2017/5/21
 * Time: 12:23
 */

// 机构表手续费汇总 = 机构流水表当日手续费汇总
// 客户表手续费汇总 = 客户流水表当日手续费汇总
// 机构流水表手续费汇总 = -客户流水表手续费汇总（外扣）
// 机构流水表手续费汇总 = 历史订单表手续费汇总
// 机构流水异常订单汇总 = -异常订单手续费汇总
// 客户流水异常订单汇总 = -异常订单手续费汇总
// 机构表交易所手续费汇总 = 机构流水表交易所当日手续费汇总
// 机构流水表交易所手续费汇总 = 机构流水表手续费汇总 * （100% - 运营中心返佣比例 - 会员单位返佣比例 - 系统开发商返佣比例）
// 机构流水表中各运营中心的手续费汇总 = 该运营中心下所有客户的手续费汇总 * 运营中心返佣比例
// 机构流水表中各会员单位的手续费汇总 = 该会员单位下所有客户的手续费汇总 * 会员单位返佣比例


include 'pub.php'; // 公共函数

// 当日时间定义
$start_time = json_decode($gpstr->get('u_trading_times')['val'], true)['start_time']; // 开盘时间 todo
$today_start = strtotime(date('Y-m-d')) + $start_time * 3600; // 当日开始时间

// 手续费分配比例
$fee_m = json_decode($gpstr->get('u_service_fee')['val'], true)['fourth'] / 100; // 会员单位
$fee_o = json_decode($gpstr->get('u_service_fee')['val'], true)['third'] / 100; // 运营中心
$fee_d = json_decode($gpstr->get('u_service_fee')['val'], true)['dev'] / 100; // 系统开发商

// 内扣、外扣
$fee_in = $gp->get('fee_in')['val']; // 手续费内扣外扣，0外扣，1内扣

// 交易所id
$sql = 'SELECT id FROM zzx_jg_company WHERE jg_type = 1';
$res = $my_conn->query($sql);
$jys_id = mysqli_fetch_array($res, MYSQLI_ASSOC)['id'];

// 机构表手续费汇总
$sql = 'SELECT Sum(zzx_jg_company.service_fee) as fee FROM zzx_jg_company';
$res = $my_conn->query($sql);
$fee_jq = mysqli_fetch_array($res, MYSQLI_ASSOC)['fee'];
echo "机构表手续费汇总：$fee_jq\n";

// 机构表交易所手续费汇总
$where = ' WHERE zzx_jg_company.id = ' . $jys_id;
$res = $my_conn->query($sql . $where);
$fee_jys = mysqli_fetch_array($res, MYSQLI_ASSOC)['fee'];
echo "机构表交易所手续费汇总：$fee_jys\n";

// 机构流水表手续费汇总
$sql = 'SELECT Sum(zzx_balanceinfo_jg.atm) as pl FROM zzx_balanceinfo_jg WHERE zzx_balanceinfo_jg.dealtype = 3';
$sql2 = 'SELECT Sum(zzx_balanceinfo_jg.atm) as pl FROM zzx_balanceinfo_jg WHERE zzx_balanceinfo_jg.dealtype = 5';
$res = $my_conn->query($sql);
$res2 = $my_conn->query($sql2);
$fee_jqls_3 = mysqli_fetch_array($res, MYSQLI_ASSOC)['pl'];
$err_jgls = mysqli_fetch_array($res2, MYSQLI_ASSOC)['pl'];
$fee_jqls = $fee_jqls_3 + $err_jgls;
echo "机构流水表手续费汇总：$fee_jqls\n";

// 机构流水异常订单汇总
echo "机构流水异常订单汇总：$err_jgls\n";

// 机构流水表当日手续费汇总
$where = " AND createtime >=$today_start";
$res = $my_conn->query($sql . $where);
$res2 = $my_conn->query($sql2 . $where);
$fee_jqls_today = mysqli_fetch_array($res, MYSQLI_ASSOC)['pl'] + mysqli_fetch_array($res2, MYSQLI_ASSOC)['pl'];
echo "机构流水表当日手续费汇总：$fee_jqls_today\n";

// 机构流水表交易所手续费汇总
$where = ' AND zzx_balanceinfo_jg.jid = ' . $jys_id;
$res = $my_conn->query($sql . $where);
$res2 = $my_conn->query($sql2 . $where);
$fee_jys_jqls = mysqli_fetch_array($res, MYSQLI_ASSOC)['pl'] + mysqli_fetch_array($res2, MYSQLI_ASSOC)['pl'];
echo "机构流水表交易所手续费汇总：$fee_jys_jqls\n";

// 机构流水表交易所当日手续费汇总
$where = " AND zzx_balanceinfo_jg.jid = {$jys_id}  AND createtime >=$today_start";
$res = $my_conn->query($sql . $where);
$res2 = $my_conn->query($sql2 . $where);
$fee_jys_today = mysqli_fetch_array($res, MYSQLI_ASSOC)['pl'] + mysqli_fetch_array($res2, MYSQLI_ASSOC)['pl'];
echo "机构流水表交易所当日手续费汇总：$fee_jys_today\n";

// 客户表手续费汇总（包括异常）
$sql = 'SELECT Sum(zzx_wp_userinfo.service_fee) as fee FROM zzx_wp_userinfo';
$res = $my_conn->query($sql);
$fee_kh = mysqli_fetch_array($res, MYSQLI_ASSOC)['fee'];
echo "\n客户表手续费汇总（包括异常）：$fee_kh\n";

// 客户流水表手续费汇总（包括异常）###########注：手续费另外字段
$sql = 'SELECT Sum(zzx_balanceinfo_kh.proce) as fee FROM zzx_balanceinfo_kh WHERE zzx_balanceinfo_kh.dealtype BETWEEN 3 AND 5';
$res = $my_conn->query($sql);
$fee_khls = mysqli_fetch_array($res, MYSQLI_ASSOC)['fee'];
echo "客户流水表手续费汇总（包括异常）：$fee_khls\n";

// 客户流水异常订单汇总
$sql2 = 'SELECT Sum(zzx_balanceinfo_kh.proce) as fee FROM zzx_balanceinfo_kh WHERE zzx_balanceinfo_kh.dealtype = 5';
$res = $my_conn->query($sql2);
$fee_khls_back = mysqli_fetch_array($res, MYSQLI_ASSOC)['fee'];
echo "客户流水表手续费异常退回汇总：$fee_khls_back\n";

// 客户流水表当日手续费汇总
$where = " AND createtime >=$today_start";
$res = $my_conn->query($sql . $where);
$fee_khls_today = mysqli_fetch_array($res, MYSQLI_ASSOC)['fee'];
echo "客户流水表当日手续费汇总：$fee_khls_today\n";

// 历史订单表手续费汇总
$sql = 'SELECT sum(service_fee) as fee FROM zzx_order_history';
$res = $my_conn->query($sql);
$history_fee = mysqli_fetch_array($res, MYSQLI_ASSOC)['fee'];
echo "\n历史订单表手续费汇总：$history_fee\n";

// 异常订单表手续费汇总
$sql = 'SELECT sum(service_fee) as fee FROM zzx_order_error';
$res = $my_conn->query($sql);
$error_fee = mysqli_fetch_array($res, MYSQLI_ASSOC)['fee'];
echo "异常订单表手续费汇总：$error_fee\n";

// 验证 ##################################################################################
echo "\n验证：\n";


// 机构表手续费汇总 = 机构流水表当日手续费汇总
if (bccomp($fee_jq, $fee_jqls_today, 4) == 0) {
    echo "机构表手续费汇总 = 机构流水表当日手续费汇总：ok\n";
} else {
    echo "机构表手续费汇总$fee_jq = 机构流水表当日手续费汇总$fee_jqls_today ：error\n";
}

// 客户表手续费汇总 = 客户流水表当日手续费汇总
if (bccomp($fee_kh, $fee_khls_today, 4) == 0) {
    echo "客户表手续费汇总 = 客户流水表当日手续费汇总：ok\n";
} else {
    echo "客户表手续费汇总$fee_kh = 客户流水表当日手续费汇总$fee_khls_today ：error\n";
}

if (!$fee_in) {
    // 机构流水表手续费汇总 = -客户流水表手续费汇总（外扣）
    if (bccomp($fee_jqls, -$fee_khls, 4) == 0) {
        echo "机构流水表手续费汇总 = 客户流水表手续费汇总（外扣）：ok\n";
    } else {
        echo "机构流水表手续费汇总$fee_jqls = 客户流水表手续费汇总（外扣）{$fee_khls} ：error\n";
    }
}

// 机构流水表手续费汇总 = 历史订单表手续费汇总
if (bccomp($fee_jqls, $history_fee, 4) == 0) {
    echo "机构流水表手续费汇总 = 历史订单表手续费汇总：ok\n";
} else {
    echo "机构流水表手续费汇总 = 历史订单表手续费汇总：error\n";
    echo "$fee_jqls = {$history_fee} ：error\n";
}

// 机构流水异常订单汇总 = -异常订单手续费汇总
if (bccomp($err_jgls, -$error_fee, 4) == 0) {
    echo "机构流水异常订单汇总 = -异常订单手续费汇总：ok\n";
} else {
    echo "机构流水异常订单汇总 = -异常订单手续费汇总：error\n";
    echo "$err_jgls = -{$error_fee} ：error\n";
}

if($fee_in == 0){
	// 客户流水异常订单汇总 = 异常订单手续费汇总
	if (bccomp($fee_khls_back, $error_fee, 4) == 0) {
	    echo "客户流水异常订单汇总 = 异常订单手续费汇总：ok\n";
	} else {
	    echo "客户流水异常订单汇总 = 异常订单手续费汇总：error\n";
	    echo "$fee_khls_back = {$error_fee} ：error\n";
	}
}


// 机构表交易所手续费汇总 = 机构流水表交易所当日手续费汇总
if (bccomp($fee_jys, $fee_jys_today, 4) == 0) {
    echo "机构表交易所手续费汇总 = 机构流水表交易所当日手续费汇总：ok\n";
} else {
    echo "机构表交易所手续费汇总$fee_jys = 机构流水表交易所当日手续费汇总$fee_jys_today ：error\n";
}

// 机构流水表交易所手续费汇总 = 机构流水表手续费汇总 * （100% - 运营中心返佣比例 - 会员单位返佣比例 - 系统开发商返佣比例）
if (bccomp($fee_jys_jqls, $fee_jqls * (1 - $fee_m - $fee_o - $fee_d)) !== 0) {
    echo "交易所手续费比例$fee_jys_jqls = $fee_jqls * (1 - $fee_m - $fee_o - $fee_d) ：error\n\n\n";
} else {
    echo "交易所手续费比例 ：ok\n\n\n";
}

// 机构流水表中各运营中心的手续费汇总 = 该运营中心下所有客户的手续费汇总 * 运营中心返佣比例
v_jqfee(3);

// 机构流水表中各会员单位的手续费汇总 = 该会员单位下所有客户的手续费汇总 * 会员单位返佣比例
v_jqfee(4);


/**
 * 验证各机构的手续费
 * @param $jq_type 机构类型，3运营中心，4会员单位
 */
function v_jqfee($jq_type)
{
    global $my_conn, $fee_in, $fee_o, $fee_m;
    if ($jq_type == 3) {
        $jq_type_str = "运营中心";
        $fee_i = $fee_o; // 手续费比例
    }
    if ($jq_type == 4) {
        $jq_type_str = "会员单位";
        $fee_i = $fee_m; // 手续费比例
    }
    // 机构表
    $sql = "SELECT c.id FROM zzx_jg_company AS c WHERE c.jg_type = $jq_type";
    $res = $my_conn->query($sql);
    while ($row = mysqli_fetch_assoc($res)) {
        // 机构流水表中各机构的手续费汇总
        $where = " WHERE zzx_balanceinfo_jg.dealtype = 3 AND zzx_balanceinfo_jg.jid = {$row['id']}";
        $where2 = " WHERE zzx_balanceinfo_jg.dealtype = 5 AND zzx_balanceinfo_jg.jid = {$row['id']}";
        $sql = "SELECT Sum(zzx_balanceinfo_jg.atm) as fee FROM zzx_balanceinfo_jg ";
        $re = $my_conn->query($sql . $where);
        $re2 = $my_conn->query($sql . $where2);
        $fee_jq = mysqli_fetch_array($re, MYSQLI_ASSOC)['fee'] + mysqli_fetch_array($re2, MYSQLI_ASSOC)['fee'];

        // 客户流水表手续费汇总
        $sql = 'SELECT Sum(b.proce) as fee FROM zzx_balanceinfo_kh AS b JOIN zzx_wp_userinfo AS u 
ON b.uid = u.uid WHERE b.dealtype BETWEEN 3 AND 5 AND u.jid_level' . $jq_type . "={$row['id']}";
        $re = $my_conn->query($sql);
        $fee_khls = mysqli_fetch_array($re, MYSQLI_ASSOC)['fee'];

        // 历史订单表手续费汇总
        $sql = 'SELECT sum(h.service_fee) as fee FROM zzx_order_history AS h JOIN zzx_wp_userinfo AS u
ON h.uid = u.uid WHERE u.jid_level' . $jq_type . "={$row['id']}";
        $re = $my_conn->query($sql);
        if ($re){
            $history_fee = mysqli_fetch_array($re, MYSQLI_ASSOC)['fee'];
        } else {
            $history_fee = 0;
        }

        // 异常订单表手续费汇总
        $sql = 'SELECT sum(e.service_fee) as fee FROM zzx_order_err AS e JOIN zzx_wp_userinfo AS u
ON e.uid = u.uid WHERE u.jid_level' . $jq_type . "={$row['id']}";
        $re = $my_conn->query($sql);
        if ($re) {
            $error_fee = mysqli_fetch_array($re, MYSQLI_ASSOC)['fee'];
        } else {
            $error_fee = 0;
        }

        // 验证流水表
        if (!$fee_in) { // 外扣
            if (bccomp($fee_jq, -$fee_khls * $fee_i, 4) !== 0) {
                $v_err = true;
                echo "机构流水表{$jq_type_str}的手续费汇总 = 该所有客户的手续费汇总 * 返佣比例：error \n";
                echo "$fee_jq = -{$fee_khls} * {$fee_i} error，jid：{$row['id']}\n";
            }
        }
        // 验证订单表
        if (bccomp($fee_jq, ($history_fee - $error_fee) * $fee_i, 4) !== 0) {
            $v_err = true;
            echo "机构流水表{$jq_type_str}的手续费汇总 = 该所有订单表的手续费汇总 * 返佣比例：error \n";
            echo "$fee_jq = ($history_fee - $error_fee) * {$fee_i} error，jid：{$row['id']}\n";
        }
    }
    if (!isset($v_err)) {
        echo "各个{$jq_type_str}手续费 = 该{$jq_type_str}下所有客户的手续费汇总 * {$jq_type_str}返佣比例：ok\n";
    }
}

echo "\n\n";
